package com.ac.input.web.action.imp;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.springframework.jdbc.core.JdbcTemplate;

import com.caac.core.web.action.BaseAction;
import com.caac.core.web.form.BaseForm;
import com.ac.input.util.UUID;
import com.ac.input.web.form.ImpDataForm;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright AgileCentury 2004</p>
 * <p>Company: AgileCentury</p>
 * @author <a href="mailto:project@agilecentury.com.cn">Project Group</a>
 * @version 1.0
 */

public class ImpStep2Action extends BaseAction {
    protected ActionForward doExecute(ActionMapping mapping, BaseForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
    	String ignore = request.getParameter("ignore");
    	if("1".equals(ignore)){
    	   	return forward(mapping);
    	}
    	
        ImpDataForm myForm = (ImpDataForm) form;
		List<String> sqlList = new ArrayList<String>();
		InputStream is = null;
		Workbook wb = null;
		JdbcTemplate jt = this.getJdbcTemplate();
		try {
			is = myForm.getFormFile().getInputStream();
			wb = Workbook.getWorkbook(is);
			Sheet st = wb.getSheet(0);
			String sql = null;
			sql = "UPDATE HND_INP_DEF_ITM SET COL_NAME=NULL,IS_VISIBLE=0 WHERE INP_CODE='"+myForm.getFromCode()+"'";
			sqlList.add(sql);
			
			int iRow = 0;
			int iCol = 0;
			int rowCount = st.getRows();
			int colCount = st.getColumns();
			String tmp = null;
			String v0 = st.getCell(iCol, iRow).getContents().trim();
			while (!"".equals(v0)) {
				tmp = st.getCell(iCol, iRow).getContents().trim();
				sql = "UPDATE HND_INP_DEF_ITM SET COL_NAME='" + tmp + "',IS_VISIBLE=1 WHERE INP_CODE='"+myForm.getFromCode()+"' AND VIS_NO="+(iCol+2);
				sqlList.add(sql);
				iCol++;
				if (iCol > colCount - 1) {
					break;
				}
				v0 = st.getCell(iCol, iRow).getContents().trim();
			}
			
			colCount = iCol;
			iRow++;
			v0 = st.getCell(0, 0).getContents().trim();
			sql = "DELETE FROM HND_IMP_TMP";
			sqlList.add(sql);
			String sF = null;
			String sV = null;
			while (!"".equals(v0)) {
				sF = "";
				sV = "";
				for (iCol=0; iCol<colCount; iCol++){
					sF += "COL"+(iCol+1)+",";
					sV += "'"+st.getCell(iCol, iRow).getContents().trim()+"',";
				}
				sF += "ID";
				sV += "'"+UUID.randomUUID().toString()+"'";
				sql = "INSERT INTO HND_IMP_TMP("+sF+") VALUES("+sV+")";
				sqlList.add(sql);
				
				iRow++;
				if (iRow > rowCount - 1) {
					break;
				}
				v0 = st.getCell(0, iRow).getContents().trim();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (is != null) {
				is.close();
			}
		}
		jt.batchUpdate((String[]) (sqlList.toArray(new String[sqlList.size()])));
		myForm.setFormFile(null);
		
    	return forward(mapping);
	}
}
